clear all
set more off 

******************************************************************
//// 01-PROCESS_CITATION: IMPORT AND SAVE RAW CITATIONS DATA ////
******************************************************************


// INITIALIZE LOOP //
forval y=2005/2018 {


// READ IN DATA FOR YEAR Y //
import delimited using "${raw}/UTC/citation_`y'.txt", clear delim("|")


// RENAME VARIABLES //
// NOTE: COMMENTED OUT VARIABLES WILL BE DROPPED //
rename v1  CitationNumber
// rename v2  CheckDigit
rename v3  CountyNum
rename v4  JurisNumber
// rename v5  CityName
rename v6  IssueAgencyType
// rename v7  IssueAgencyCode
// rename v8  IssueAgencyName
// rename v9  DOTW   // Note: stata can get this from date //
rename v10 OffenseDate
rename v11 OffenseTime
rename v12 OffenseTimeAMPM
rename v13 DriverFirstName
rename v14 DriverMiddleName
rename v15 DriverLastName
rename v16 DriverSuffix
rename v17 HomeAddress
// rename v18 AddDiffLicense
rename v19 HomeCity
rename v20 HomeState
rename v21 ZipCode
// rename v22 Telephone
rename v23 BirthDate
rename v24 Race
rename v25 Sex
// rename v26 Height
rename v27 DLnumber
rename v28 DLstate
rename v29 DLclass
// rename v30 DLExpYear
rename v31 CommVehicleCode
rename v32 VehicleYear
rename v33 VehicleMake
rename v34 VehicleStyle
// rename v35 VehicleColor
// rename v36 HazardousMaterials
// rename v37 VehicleTagNum
// rename v38 VehicleTrailerTagNum
rename v39 VehicleState
// rename v40 VehicleTagExpirationYear
rename v41 CompanionCitation
// rename v42 ViolationLocation
// rename v43 DistanceFeet
// rename v44 DistanceMiles
// rename v45 DirectionN
// rename v46 DirectionS
// rename v47 DirectionE
// rename v48 DirectionW
// rename v49 OfNode
rename v50 ActualSpeed
rename v51 PostedSpeed
rename v52 Hwy4Lane
rename v53 HwyInterstate
rename v54 Viol_Careless
rename v55 Viol_Device
rename v56 Viol_Row
rename v57 Viol_Lane
rename v58 Viol_Passing
rename v59 Viol_ChildRestraint
rename v60 Viol_DUI
// rename v61 BloodAlcohol
rename v62 Viol_SeatBelt
rename v63 Viol_Equipment
rename v64 Viol_TagLess
rename v65 Viol_TagMore
rename v66 Viol_Insurance
rename v67 Viol_ExpireDriverLicense
rename v68 Viol_ExpireDriverLicenseMore
rename v69 Viol_InvalidDriverLicense
rename v70 Viol_SuspendedLicense
// rename v71 OtherComments
rename v72 Viol_Code
// rename v73 Filler
// rename v74 StateStatuteInd
// rename v75 Section
// rename v76 Sub_Section
rename v77 Crash
rename v78 PropertyDamage
// rename v79 PropertyDamageAmt
rename v80 Injury
rename v81 SeriousInjury
rename v82 FatalInjury
rename v83 ArrestMethod
// rename v84 CrimViolCourtAppReq
// rename v85 InfractCourtApp
// rename v86 InfractCourtAppNot
// rename v87 CourtDate
// rename v88 CourtTime
// rename v89 CourtName
// rename v90 CourtTimeAMPM
// rename v91 CourtAddress
// rename v92 CourtCity
// rename v93 CourtState
// rename v94 CourtZip
// rename v95 ArrestDeliveredTo
// rename v96 ArrestDeliveredDate
// rename v97 OfficerRank
rename v98 OfficerFirstName
rename v99 OfficerMiddleName
rename v100 OfficerLastName
// rename v101 OfficerBadgeNum
rename v102 OfficerID
// rename v103 OfficerUnit
rename v104 BAL08
// rename v105 DUI_Refuse
// rename v106 DUI_LicenseSurrender
// rename v107 DUI_License_RSN
// rename v108 DUI_Eligible_Y
// rename v109 DUI_Eligible_RSN
// rename v110 DUI_BAR_Office
// rename v111 Status
// rename v112 AggressiveDriverFlag
rename v113 CriminalIndicator
rename v114 FineAmount
// rename v115 FormVersion
// rename v116 IssueArrestDate
// rename v117 OfficerDeliveryVerification
// rename v118 DueDate
// rename v119 Motorcycle
// rename v120 PassengerVehicle16
// rename v121 OfficerReExamFlag
// rename v122 DUIViol_Under18
rename v123 EcitationInd
// rename v124 NameChange
rename v125 CommercialDL
rename v126 GPSLat
rename v127 GPSLong
rename v128 Viol_SignalRed
rename v129 Viol_WorkersPres
rename v130 Viol_HandHeld
rename v131 Viol_SchoolZone
// rename v132 AgencyID
// rename v133 PermReg
// rename v134 SpeedDeviceID
// rename v135 DLSeize
// rename v136 Business
// rename v137 Filler2

// DROP VARIABLES THAT ARE NOT RENAMES //
cap drop v*

// TRIM CITATION //
replace CitationNumber = trim(upper(CitationNumber))

/// CONVERT DATES TO STATA FORMAT ////
gen Date=date(OffenseDate,"MDY")
format Date %td
drop OffenseDate
ren Date OffenseDate

gen OffenseHour=substr(OffenseTime,1,2)
destring OffenseHour, replace force
drop OffenseTime

gen OffenseTimeAM=.
replace OffenseTimeAM=0 if OffenseTimeAMPM=="A"|OffenseTimeAMPM=="P"
replace OffenseTimeAM=1 if OffenseTimeAMPM=="A"
drop OffenseTimeAMPM

gen Dob=date(BirthDate,"MDY")
format Dob %td
drop BirthDate
ren Dob BirthDate

*gen Cd=date(CourtDate,"MDY")
*format Cd %td
*drop CourtDate
*ren Cd CourtDate



//// CLEAN UP VIOLATION CODES ////
//// GENERATE INDICATOR FOR VIOL_OTHER IF ANY OF THE VIOLATION FLAGS ARE "YES" ////
ren Viol_Code Violation
local k=1
foreach x of varlist Viol_* {
	gen Viol_`k'=(`x'=="Y"|`x'=="1")
	drop `x'
	local ++k
}
egen ViolOther=rowmax(Viol_*)
drop Viol_*
ren ViolOther Viol_Other
ren Violation Viol_Code



//// OTHER SPACE-SAVING CLEAN UP ////
//// CONVERT FROM STRING TO NUMERIC WHERE POSSIBLE ////

gen Female=Sex=="F"
drop Sex

cap destring VehicleYear, replace force

replace ZipCode=substr(ZipCode,1,5)
destring ZipCode, replace force

// Other Indicator Vars //
#delimit ;
foreach x in CommVehicleCode CompanionCitation Hwy4Lane HwyInterstate 
	Crash PropertyDamage Injury SeriousInjury FatalInjury BAL08 CriminalIndicator
	EcitationInd CommercialDL { ;
		ren `x' `x'_Old  ;
		gen `x'=(`x'_Old=="Y"|`x'_Old=="1") ;
		drop `x'_Old ;
	} ;
#delimit cr
ren CommercialDL DLcommercial
ren CommVehicleCode VehicleCommercial



//// CONVERT TO NUMERIC (ENSURES SUCCESSFUL APPENDS WITH OTHER YEARS) ////
cap destring Viol_Code, replace force
cap destring FineAmount, replace force
* cap tostring OfficerID, replace force
cap destring OfficerID, replace force
cap destring ArrestMethod, replace force
cap destring ActualSpeed, replace force
cap destring PostedSpeed, replace force
*cap destring IssueAgencyCode, replace force
cap destring GPSLat, replace force
cap destring GPSLon, replace force


/// SPEED VARIABLES ///
replace PostedSpeed = round(Posted,5)
gen SpeedDiff = ActualSpeed - PostedSpeed


//// ORDER VARIABLES HOW WE WANT THEM ////
#delimit ;
order CitationNumber CountyNum JurisNum OffenseDate OffenseHour OffenseTimeAM
	Viol_Code Viol_Other FineAmount CompanionCitation ActualSpeed PostedSpeed 
	SpeedDiff DriverFirstN DriverLastN Female Race BirthDate
	ZipCode DLnumber DLstate DLclass DLcommercial 
	VehicleYear VehicleMake VehicleStyle VehicleState
	VehicleCommercial IssueAgencyType 
	OfficerFirst OfficerMiddle OfficerLast OfficerID 
	ArrestMethod Criminal Ecitation Crash Property Injury SeriousInjury FatalInjury 
	BAL08 Hwy4Lane HwyInterstate GPSLat GPSLong ;
#delimit cr
	

/*
//// ANOTHER SPACE-SAVING MEASURE ////
//// CUT STRINGS LARGER THAN 50 CHARS DOWN TO FIRST 50 /////
#delimit ;
foreach x in DriverFirst DriverMiddle DriverLast VehicleMake VehicleStyle
	IssueAgencyName OfficerFirst OfficerMiddle OfficerLast OfficerUnit { ;
		replace `x'=substr(`x',1,50) if length(`x')>50 ;
} ;
#delimit cr
*/
replace DriverFirst=substr(DriverFirst,1,20) if length(DriverFirst)>20
recast str20 DriverFirst
replace DriverLast=substr(DriverLast,1,30) if length(DriverLast)>30
recast str30 DriverLast
replace VehicleMake=substr(VehicleMake,1,20) if length(VehicleMake)>20
recast str20 VehicleMake
replace VehicleStyle=substr(VehicleStyle,1,20) if length(VehicleStyle)>20
recast str20 VehicleStyle
replace DLnumber = substr(DLnumber,1,15) if length(DLnumber)>15
recast str15 DLnumber


//// STORE TEMP FILE FOR YEAR Y ////
qui compress
saveold "${temp}/t_`y'", replace

}


/// APPEND TEMPORARY FILES ////
use "${temp}/t_2005", clear
forval y=2006/2018 {
	qui append using "${temp}/t_`y'"
}


//// DROP ~350 OBS WITH DUPLICATE CITATION X COUNTY NUMBERS ///
duplicates tag CitationNumber CountyNum, gen(tag)
drop if tag>0
drop tag


//// COMPRESS AND STORE 
qui compress
saveold "${temp}/process-citation", replace

// Delete Intermediate Files //
forval y=2005/2018 {
	rm "${temp}/t_`y'.dta" 
}













